package Utils;

import Service.DBService;

import javax.swing.*;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 文永达
 * @title: query
 * @projectName jiaowuglxt
 * @description: TODO
 * @date 2021/1/20 11:36
 */
public class QueryUtil {
    DBService dbService = new DBService();
    /**
     * @description: <br>
     * 〈查询班级〉
     * @Param: []
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 11:42
     */
    public void queryClazz(){
        try {
            String sql1 = "select id,gradeid,gradename,majorid,majorname,clazzname from clazz";
            ResultSet rs =  dbService.querySQL(sql1);
            System.out.printf("%-4s\t\t%-4s\t\t%-4s\t\t%-4s\t\t%-10s\t\t%-4s\n","班级编号","年级编号","专业编号","年级名称","专业名称","班级名称");
            while (rs.next()){
                int id = rs.getInt("id");
                int gradeid = rs.getInt("gradeid");
                int majorid = rs.getInt("majorid");
                String gradename = rs.getString("gradename");
                String majorname = rs.getString("majorname");
                String clazzname = rs.getString("clazzname");
                System.out.printf("%-6d\t\t%-6d\t\t%-6d\t\t%-6s\t\t%-10s\t\t%-6s\n",id,gradeid,majorid,gradename,majorname,clazzname);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * @description: <br>
     * 〈查询班级根据年级，专业，方法重载〉
     * @Param: [gradename, majorname]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 11:42
     */
    public void queryClazz(String gradename,String majorname){
        try {
            String sql1 = "select id,gradeid,gradename,majorid,majorname,clazzname from clazz where gradename = '"+gradename+"' and majorname = '"+majorname+"'";
            ResultSet rs = dbService.querySQL(sql1);
            System.out.printf("%-4s\t\t%-4s\t\t%-4s\t\t%-4s\t\t%-10s\t\t%-4s\n","班级编号","年级编号","专业编号","年级名称","专业名称","班级名称");
            while (rs.next()){
                int id = rs.getInt("id");
                int gradeid = rs.getInt("gradeid");
                int majorid = rs.getInt("majorid");
                String gradename1 = rs.getString("gradename");
                String majorname1 = rs.getString("majorname");
                String clazzname = rs.getString("clazzname");
                System.out.printf("%-6d\t\t%-6d\t\t%-6d\t\t%-6s\t\t%-10s\t\t%-6s\n",id,gradeid,majorid,gradename1,majorname1,clazzname);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    /**
     * @description: <br>
     * 〈按照班级查询学生〉
     * @Param: [clazzid]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 11:41
     */
    public void querystudentByClazz(int clazzid){
        try {
            String sql1 = "SELECT s.stunum,s.stuname,s.stusex,c.gradename,c.majorname,c.clazzname FROM student s INNER JOIN clazz c ON s.clazzid = c.id and s.clazzid = "+clazzid+"";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                rs1.previous();
                System.out.printf("%-6s\t\t%-4s\t\t%-4s\t\t%-4s\t\t%-10s\t\t%-4s\n","学生学号","学生姓名","学生性别","年级","专业名称","班级名称");
                while (rs1.next()){
                    String stunum = rs1.getString(1);
                    String stuname = rs1.getString(2);
                    String stusex = rs1.getString(3);
                    String gradename = rs1.getString(4);
                    String majorname = rs1.getString(5);
                    String clazzname = rs1.getString(6);
                    System.out.printf("%-10s\t\t%-4s\t\t%-6s\t\t%-6s\t\t%-10s\t\t%-6s\n",stunum,stuname,stusex,gradename,majorname,clazzname);
                }
            }else {
                System.out.println("此班级还没有学生");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * @description: <br>
     * 〈统计每个班级学生总人数〉
     * @Param: [clazzid]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 11:41
     */
    public void countStudentNumByClazz(int clazzid){
        try {
            String sql1 = "SELECT c.gradename,c.majorname,c.clazzname,count(s.clazzid) FROM student s,clazz c WHERE s.clazzid = c.id AND s.clazzid = "+clazzid+"";
            ResultSet rs1 = dbService.querySQL(sql1);
            rs1.next();
            String gradename = rs1.getString(1);
            String majorname = rs1.getString(2);
            String clazzname = rs1.getString(3);
            int count = rs1.getInt(4);
            if(count!=0){
                System.out.printf("%-4s\t\t%-10s\t\t%-4s\t\t%-4s\n","年级名称","专业名称","班级名称","人数");
                System.out.printf("%-6s\t\t%-10s\t\t%-6s\t\t%d%s\n",gradename,majorname,clazzname,count,"人");
            }else {
                System.out.println("此班级人数为0");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * @description: <br>
     * 〈通过学生学号查询学生〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/21 20:22
     */
    public int queryStudentBystunum(String stunum){
        try {
            String sql1 = "SELECT s.id,s.stuname,s.stusex,c.gradename,c.majorname,c.clazzname FROM student s,clazz c WHERE s.clazzid = c.id AND s.stunum = '"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                int id = rs1.getInt(1);
                String stuname = rs1.getString(2);
                String stusex = rs1.getString(3);
                String gradename = rs1.getString(4);
                String majorname = rs1.getString(5);
                String clazzname = rs1.getString(6);
                System.out.printf("%-4s\t\t%-4s\t\t%-4s\t\t%-4s\t\t%-10s\t\t%s\n","学生编号","学生姓名","学生性别","年级","专业名称","班级");
                System.out.printf("%-6d\t\t%-4s\t\t%-4s\t\t%-4s\t\t%-10s\t\t%s\n",id,stuname,stusex,gradename,majorname,clazzname);
                return 1;
            }else {
                String sql2 = "SELECT id,stuname,stusex FROM student WHERE stunum = '"+stunum+"'";
                ResultSet rs2 = dbService.querySQL(sql2);
                if(rs2.next()){
                    System.out.println("查询到此学生处于休学状态");
                    System.out.printf("%-4s\t\t%-4s\t\t%-4s\n","学生编号","学生姓名","学生性别");
                    int id1 = rs2.getInt(1);
                    String stuname1 = rs2.getString(2);
                    String stusex1 = rs2.getString(3);
                    System.out.printf("%-4d\t\t%-4s\t\t%-4s\n",id1,stuname1,stusex1);
                    return 1;
                }
                else {
                    System.out.println("没有查询到此学生信息，请确认学生信息是否有误");
                    return 0;
                }
            }

        } catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * @description: <br>
     * 〈按照学生编号可以查看每个学生的详细学习变动情况〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/22 13:08
     */
    public void thechangesofStudybyStunum(String stunum){
        try {
            String sql1 = "SELECT si.interpretation,s.id FROM studentinfo si,student s where s.id = si.stuid AND s.stunum = '"+stunum+"' AND si.title = '学生降班'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                rs1.previous();
                while (rs1.next()){
                    String relegation = rs1.getString(1);
                    System.out.println();
                    System.out.println("该生降班情况为：");
                    System.out.println(relegation);
                    System.out.println();
                }
            }else{
                System.out.println();
                System.out.println("该生不存在降班情况");
            }

            String sql2 = "SELECT si.interpretation,s.id FROM studentinfo si,student s where s.id = si.stuid AND s.stunum = '"+stunum+"' AND si.title = '学生休学'";
            ResultSet rs2 = dbService.querySQL(sql2);
            if (rs2.next()){
                rs2.previous();
                while (rs2.next()){
                    String quitschool = rs2.getString(1);
                    System.out.println();
                    System.out.println("该生休学情况为：");
                    System.out.println(quitschool);
                    System.out.println();
                }
            }else {
                System.out.println();
                System.out.println("该生不存在休学情况");
            }
            String sql3 = "SELECT si.interpretation,s.id FROM studentinfo si,student s where s.id = si.stuid AND s.stunum = '"+stunum+"' AND si.title = '学生信息修改'";
            ResultSet rs3 = dbService.querySQL(sql3);
            if (rs3.next()){
                rs3.previous();
                while (rs3.next()){
                    String updateinfo = rs3.getString(1);
                    System.out.println();
                    System.out.println("该生信息变动情况为：");
                    System.out.println(updateinfo);
                    System.out.println();
                }
            }else {
                System.out.println();
                System.out.println("该生不存在信息变动情况");
            }
            String sql4 = "SELECT si.interpretation,s.id FROM studentinfo si,student s where s.id = si.stuid AND s.stunum = '"+stunum+"' AND si.title = '学生复课'";
            ResultSet rs4 = dbService.querySQL(sql4);
            if (rs4.next()){
                rs4.previous();
                while (rs4.next()){
                    String resumeclas = rs4.getString(1);
                    System.out.println();
                    System.out.println("该生复课情况为：");
                    System.out.println(resumeclas);
                }
            }else {
                System.out.println();
                System.out.println("该生不存在复课情况");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈统计所有学生中事最多的学生〉
     * @Param: []
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/24 13:03
     */
    public void queryStuinfoAllStudent(){
        try {
            String sql1 = "select s.stunum,s.stuname,c.gradename,c.majorname,c.clazzname from student s,studentinfo si,clazz c where s.id = si.stuid and s.clazzid = c.id GROUP BY si.stuid, s.stuname ORDER BY count(si.stuid) desc limit 1";
            ResultSet rs1 = dbService.querySQL(sql1);
            System.out.println("所有学生中事最多的学生");
            System.out.printf("%-8s\t\t%-6s\t\t%-6s\t\t%-12s\t\t%-6s\n","学生学号","学生姓名","年级","专业","班级");
            if (rs1.next()){
                String stunum = rs1.getString(1);
                String stuname = rs1.getString(2);
                String gradename = rs1.getString(3);
                String majorname = rs1.getString(4);
                String clazzname = rs1.getString(5);
                System.out.printf("%-10s\t\t%-6s\t\t%-6s\t\t%-10s\t\t%-6s\n",stunum,stuname,gradename,majorname,clazzname);
                thechangesofStudybyStunum(stunum);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈学生可以根据自己的学号查看自己的变动信息〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 9:41
     */
    public void queryUpdateinfoBystunum(String stunum){
        try {
            String sql1 = "SELECT si.interpretation,s.id FROM studentinfo si,student s where s.id = si.stuid AND s.stunum = '"+stunum+"' AND si.title = '学生信息修改'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if (rs1.next()){
                rs1.previous();
                while (rs1.next()){
                    String updateinfo = rs1.getString(1);
                    System.out.println("信息变动情况为：");
                    System.out.println(updateinfo);
                }
            }else {
                System.out.println("不存在信息变动情况");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈学生可以查看自己每个科目的成绩〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 10:14
     */
    public void queryScoreBystunum(String stunum){
        try {
            String sql1 = "SELECT c.coursename,cs.coursescore FROM student s ,course c ,score cs WHERE s.id = cs.stuid AND c.id = cs.courseid AND s.stunum = '"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                rs1.previous();
                System.out.println("您的各科成绩如下");;
                System.out.printf("%-8s\t\t%s\n","课程名称","课程成绩");
                while (rs1.next()){
                    String coursename = rs1.getString(1);
                    int coursescore = rs1.getInt(2);
                    System.out.printf("%-8s\t\t%d\n",coursename,coursescore);
                }
            }else {
                System.out.println("没有查询到该学生成绩");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈学生可以根据学号统计自己的一个学期的总成绩〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 10:27
     */
    public void querySUMScoreBystunum(String stunum){
        try {
            String sql1 = "SELECT sum(cs.coursescore) FROM student s ,course c ,score cs WHERE s.id = cs.stuid AND c.id = cs.courseid AND s.stunum = '"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                int SUMcoursescore = rs1.getInt(1);
                System.out.println("您的本学期总成绩如下");
                System.out.println(SUMcoursescore);
            }else {
                System.out.println("没有查询到该学生成绩");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈学生可以根据学号统计自己的一个学期的平均成绩〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 10:33
     */
    public void queryAVGScoreBystunum(String stunum){
        try {
            String sql1 = "SELECT avg(cs.coursescore) FROM student s ,course c ,score cs WHERE s.id = cs.stuid AND c.id = cs.courseid AND s.stunum = '"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                int AVGcoursescore= rs1.getInt(1);
                System.out.println("您的本学期平均成绩如下");
                System.out.println(AVGcoursescore);
            }else {
                System.out.println("没有查询到该学生成绩");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    /**
     * @description: <br>
     * 〈学生可以根据学号查看自己一个学期的总绩点 （绩点算法 (学科成绩+学分)*0.1）〉
     * @Param: [stunum]
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 11:12
     */
    public void querySUMPointBystunum(String stunum){
        try {
            String sql1 = "SELECT sum((cs.coursescore+c.coursecredit)*0.1) FROM student s,course c,score cs WHERE s.id = cs.stuid AND c.id = cs.courseid AND s.stunum = '"+stunum+"'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                int SUMPoint = rs1.getInt(1);
                System.out.println("您的本学期总绩点如下");
                System.out.println(SUMPoint);
            }else {
                System.out.println("没有查询到该学生成绩");
            }

        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈查询选修课〉
     * @Param: []
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/1/23 11:11
     */
    public void queryOptionalCourse(){
        try {
            String sql1 = "SELECT id,coursename,coursecredit FROM course WHERE coursetype != '主修'";
            ResultSet rs1 = dbService.querySQL(sql1);
            if(rs1.next()){
                rs1.previous();
                System.out.println("您可以选修的课程如下");
                System.out.printf("%-4s\t\t%-8s\t\t%s\n","课程编号","课程名称","课程学分");
                while (rs1.next()){
                    int courseid = rs1.getInt(1);
                    String coursename = rs1.getString(2);
                    int coursecredit = rs1.getInt(3);
                    System.out.printf("%-6d\t\t%-7s\t\t%d\n",courseid,coursename,coursecredit);
                }
            }else {
                System.out.println("没有查询到选修课");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

}
